﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;

public class DatabaseService
{
    private static SqlConnection myConnection = null;
    private static SqlDataReader myReader = null;

    private static String cntString = @"Data Source=ZHONG-PC\SQLEXPRESS;
                                        Initial Catalog=PCtaggingDB;
                                        Integrated Security=True";

    /*
     * Ready for connection
     */
    public static void initConnection()
    {
        if (myConnection == null)
        {
            myConnection = new SqlConnection(cntString);
            myConnection.Open();
        }
    }

    /*
     * Close the connection
     */ 
    public static void closeConnection()
    {
        if (myConnection != null)
        {
            myConnection.Close();
            myConnection = null;
        }
    }

    /*
     * Execute command and return the SqlDataReader 
     */ 
    public static SqlDataReader ExecuteReader(String cmdString)
    {
        try
        {
            SqlCommand myCmd = new SqlCommand(cmdString, myConnection);
            if (myReader != null && !myReader.IsClosed)
            {
                myReader.Close();
            }
            myReader = myCmd.ExecuteReader();
            return myReader;
        }
        catch(Exception e)
        {
            return null;
        }
    }

    /*
     * Get group name by id, use table "dbo.Collection_G"
     * (If not found, return null)
     */
    public static String GetGroupNameByID(int groupID)
    {
        String cmdString = @"SELECT CollectionName FROM dbo.Collections_G
                             WHERE CollectionID=" + groupID;
        SqlDataReader reader = ExecuteReader(cmdString);
        if (reader!=null && reader.Read())
        {
            return reader["CollectionName"].ToString();
        }
        return null; 
    }

    /*
     * Get member name by id, use table "dbo.CollectionMembers"
     * (If not found, return null)
     */
    public static String GetMemberNameByID(int machineID)
    {
        String cmdString = @"SELECT Name FROM dbo.CollectionMembers
                             WHERE MachineID=" + machineID;
        SqlDataReader reader = ExecuteReader(cmdString);
        if (reader != null && reader.Read())
        {
            return reader["Name"].ToString();
        }
        return null;
    }

    /*
     * Get sub-groups id, use table "dbo.Collection_SubCollections"
     */
    public static List<int> GetSubGroupsID(int groupID)
    {
        String cmdString = @"SELECT SubCollectionID FROM dbo.Collection_SubCollections
                             WHERE CollectionID=" + groupID;
        SqlDataReader reader = ExecuteReader(cmdString);
        List<int> idList = new List<int>();
        if (reader != null)
        {
            while (reader.Read())
            {
                idList.Add( Convert.ToInt32(reader["SubCollectionID"]) );
            }
        }
        return idList;
    }

    /*
     * Get sub-groups name, use function "GetSubGroupsID" & "GetGroupNameByID"
     */ 
    public static List<String> GetSubGroupsName(int groupID)
    {
        List<int> idList = GetSubGroupsID(groupID);
        List<String> nameList = new List<String>();      
        for (int i=0; i<idList.Count; i++)
        {
            nameList.Add( GetGroupNameByID(idList[i]) );
        }
        return nameList;
    }

    /*
     * Get members id, use table "dbo.Collecions_G"	& "dbo.CollectionMembers"
     */
    public static List<int> GetMembersID(int groupID)
    {
        String cmdString = @"SELECT cm.MachineID FROM dbo.CollectionMembers AS cm
                             INNER JOIN dbo.Collections_G AS cg ON cm.SiteID=cg.SiteID 
                             WHERE cg.CollectionID=" + groupID;
        SqlDataReader reader = ExecuteReader(cmdString);
        List<int> idList = new List<int>();
        if (reader != null)
        {
            while (reader.Read())
            {
                idList.Add( Convert.ToInt32(reader["MachineID"]) );
            }
        }
        return idList;
    }

    /*
     * Get members name, use table "dbo.Collecions_G" & "dbo.CollectionMembers"	
     */
    public static List<String> GetMembersName(int groupID)
    {
        String cmdString = @"SELECT cm.Name FROM dbo.CollectionMembers AS cm
                             INNER JOIN dbo.Collections_G AS cg ON cm.SiteID=cg.SiteID 
                             WHERE cg.CollectionID=" + groupID;
        SqlDataReader reader = ExecuteReader(cmdString);
        List<String> nameList = new List<String>();
        if (reader != null)
        {
            while (reader.Read())
            {
                nameList.Add(reader["Name"].ToString());
            }
        }
        return nameList;
    }

    /*
     * Get record of WM, use table "dbo.CI_CurrentRuleDetail"
     */
    public static String GetMachineRecord(int machineID, String ruleName)
    {
        String cmdString = @"SELECT PreReifiedValue FROM dbo.CI_CurrentRuleDetail
                             WHERE ItemKey=" + machineID + "AND ConstraintName='" + ruleName +"'";
        SqlDataReader reader = ExecuteReader(cmdString);
        if (reader!=null && reader.Read())
        {
            return reader["PreReifiedValue"].ToString();
        }
        return null;
    }
}